package ch.fhnw.edu.rental.test.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.Statement;

import javax.sql.DataSource;

import org.dbunit.database.DatabaseConfig;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.ext.hsqldb.HsqldbDataTypeFactory;
import org.dbunit.operation.DatabaseOperation;
import org.springframework.context.ApplicationContext;

public class JdbcDbInitializer implements DbInitializer {

	@Override
	public void resetData(ApplicationContext context) throws Exception {
		// initialize the database connection
		DataSource dataSource = (DataSource)context.getBean("dataSource");
		Connection dbconn = dataSource.getConnection();
		
		Statement stmt = dbconn.createStatement();
		//stmt.executeUpdate("CREATE TABLE RENTALS(RENTAL_ID BIGINT, RENTAL_RENTALDATE TIMESTAMP, RENTAL_RENTALDAYS INTEGER,USER_ID BIGINT,MOVIE_ID BIGINT)");
		
		stmt.executeUpdate("DROP TABLE ROLES IF EXISTS");
		stmt.executeUpdate("DROP TABLE RENTALS IF EXISTS");
		stmt.executeUpdate("DROP TABLE MOVIES IF EXISTS");
		stmt.executeUpdate("DROP TABLE USERS IF EXISTS");
		stmt.executeUpdate("DROP TABLE PRICECATEGORIES IF EXISTS");
		stmt.executeUpdate("CREATE MEMORY TABLE USERS(USER_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY," +
				"USER_EMAIL VARCHAR," +
				"USER_FIRSTNAME VARCHAR," +
				"USER_NAME VARCHAR)");
		stmt.executeUpdate("CREATE MEMORY TABLE PRICECATEGORIES(PRICECATEGORY_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY," +
				"PRICECATEGORY_TYPE VARCHAR)");
		stmt.executeUpdate("CREATE MEMORY TABLE MOVIES(MOVIE_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,MOVIE_RELEASEDATE TIMESTAMP,MOVIE_TITLE VARCHAR,MOVIE_RENTED BOOLEAN,PRICECATEGORY_FK BIGINT,CONSTRAINT FK_CATEGORY FOREIGN KEY(PRICECATEGORY_FK) REFERENCES PRICECATEGORIES(PRICECATEGORY_ID))");
		stmt.executeUpdate("CREATE MEMORY TABLE RENTALS(RENTAL_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,RENTAL_RENTALDATE TIMESTAMP,RENTAL_RENTALDAYS INTEGER,USER_ID BIGINT,MOVIE_ID BIGINT,CONSTRAINT FK_USER FOREIGN KEY(USER_ID) REFERENCES USERS(USER_ID),CONSTRAINT FK_MOVIE FOREIGN KEY(MOVIE_ID) REFERENCES MOVIES(MOVIE_ID))");
		stmt.executeUpdate("CREATE MEMORY TABLE ROLES(ROLE_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,ROLE_ROLENAME VARCHAR(255),USER_ID BIGINT,CONSTRAINT FK67A8EBDC52137E1 FOREIGN KEY(USER_ID) REFERENCES USERS(USER_ID))");

		IDatabaseConnection connection = new DatabaseConnection(dbconn);
		DatabaseConfig config = connection.getConfig();
		config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY,
				new HsqldbDataTypeFactory());
		
		InputStream stream = getClass().getResourceAsStream("dataset.xml");
		IDataSet dataSet = new FlatXmlDataSet(stream);
        
		try {
			DatabaseOperation.CLEAN_INSERT.execute(connection, dataSet);
		} finally {
			connection.close();
		}
	}

}
